package com.jy.api.dao;

import com.jy.api.domain.CouponInstance;
import com.jy.api.domain.CouponInstanceStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * Created by lihu on 2017/11/17.
 */
public interface CouponInstanceDao extends CrudRepository<CouponInstance, String> , JpaSpecificationExecutor<CouponInstance> {

    /**
     * 通过code 查询
     *
     * @param code
     * @return
     */
    CouponInstance findByCode(String code);

    CouponInstance findByOrderIdAndStauts(String orderId,CouponInstanceStatus status);

    CouponInstance findByOrderId(String orderId);



    List<CouponInstance> findByCouponIdInAndAddTimeBetween(List<String> strings ,Date begin,Date end);

    Page<CouponInstance> findAllByCouponIdAndOwnerIdAndStautsOrderByEndTimeDesc(String couponId,String ownId,CouponInstanceStatus status,Pageable pageable);

    Page<CouponInstance> findAllByCouponIdAndStautsOrderByEndTimeDesc(String couponId,CouponInstanceStatus status,Pageable pageable);


    Page<CouponInstance> findAllByCouponIdAndStautsNotAndOwnerIdOrderByEndTimeDesc(String couponId,CouponInstanceStatus status,String ownId,Pageable pageable);

    Page<CouponInstance> findAllByCouponIdAndStautsNotOrderByEndTimeDesc(String couponId,CouponInstanceStatus status,Pageable pageable);

    /**
     * 查询会员对应店铺优惠卷信息
     *
     * @param memberId
     * @param storeId
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsAndEndTimeAfterAndCouponIdIn(String memberId, String storeId,
                                                                             CouponInstanceStatus stauts, Date time,List<String> couponId,
                                                                             Pageable pageable);

    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param time
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStautsInAndEndTimeAfterAndCouponIdIn(String memberId, List<CouponInstanceStatus> stauts,
                                                                   Date time, List<String> couponId,Pageable pageable);


    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStautsAndCouponIdInOrderByValidateTimeDesc(String memberId, CouponInstanceStatus stauts,List<String> couponId,Pageable pageable);


    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStautsAndCouponIdInOrderByEndTimeDesc(String memberId, CouponInstanceStatus stauts,List<String> couponId,Pageable pageable);



    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param time
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsInAndEndTimeAfter(String memberId, String storeId,List<CouponInstanceStatus> stauts,
                                                                               Date time, Pageable pageable);

    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStautsOrderByValidateTimeDesc(String memberId, CouponInstanceStatus stauts,Pageable pageable);


    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsOrderByValidateTimeDesc(String memberId,String storeId, CouponInstanceStatus stauts,Pageable pageable);


    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStautsOrderByEndTimeDesc(String memberId, CouponInstanceStatus stauts,Pageable pageable);


    /**
     * 获取会员对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsOrderByEndTimeDesc(String memberId,String storeId, CouponInstanceStatus stauts,Pageable pageable);



    /**
     * 获取会员对应店铺对应状态优惠卷信息
     *
     * @param memberId
     * @param stauts
     * @param time
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByOwnerIdAndStautsInAndStoreIdAndEndTimeAfterAndCouponIdIn(String memberId, List<CouponInstanceStatus> stauts,
                                                                             String storeId, Date time,List<String> couponId, Pageable pageable);

    /**
     * 店铺获取核销优惠卷信息
     *
     * @param storeId
     * @param stauts
     * @param pageable
     * @return
     */
    Page<CouponInstance> findListByStoreIdAndStautsOrderByValidateTimeDesc(String storeId,
                                                                           CouponInstanceStatus stauts, Pageable pageable);

    /**
     * 获取店铺优惠卷统计
     *
     * @param storeId
     * @return
     */
    @Query(value = "select coupon_id couponId,stauts,COUNT(*) number from coupon_instance WHERE" +
            " store_id=?1 and owner_id is not null group by couponId,stauts order BY number desc ", nativeQuery = true)
    List<Object[]> queryCouponCount(String storeId);

    /**
     * 获取时间段内优惠卷信息
     *
     * @param storeId
     * @param from
     * @param end
     * @return
     */
    @Query(value = "select coupon_id couponId,stauts,COUNT(*) number from coupon_instance WHERE" +
            " store_id=?1  and owner_id is not null and update_time BETWEEN ?2 and ?3 " +
            " group by couponId,stauts order BY number desc ", nativeQuery = true)
    List<Object[]> queryCouponCount(String storeId, Date from, Date end);


    /**
     * 查询同用户领取该类优惠卷分享信息
     *
     * @param openId
     * @param couponId
     * @return
     */
    List<CouponInstance> findByOpenIdAndCouponIdAndRefCouponCodeNotNull(String openId, String couponId);


    CouponInstance findByOpenIdAndRefCouponCode(String openId, String couponId);

    long countByOwnerIdAndCouponId(String id, String couponId);

    List<CouponInstance> findByStoreIdAndOwnerId(String storeId,String ownerId);

    @Query(value = "SELECT `order_product_id`  FROM `coupon_instance` WHERE `store_id` =? AND `stauts` ='VERIFIED'" ,nativeQuery = true)
    List<String> findByProduct(String id);



    @Modifying
    @Transactional
    @Query(value = "UPDATE `coupon_instance` set `stauts` ='EXPIRED' WHERE `owner_id`=?  AND `store_id` =?   AND `end_time` <=? AND `stauts` ='NORMAL' " ,nativeQuery = true)
    void updateUserCouponStatus(String memberId, String storeId,Date date);
//SET `promotion_name`='但是是否,',`remark`='打款,'
    @Modifying
    @Transactional
    @Query(value = "UPDATE `coupon_instance` set `stauts` ='NORMAL',`end_time` =?  WHERE `coupon_id`=?    AND `stauts` not in ('VERIFIED','STOP') " ,nativeQuery = true)
    void updateUserCouponStatusByDate( Date date,String couponId);


    @Query(value = "SELECT ifnull(COUNT(*),0)  FROM `coupon_instance` WHERE `coupon_id` =? AND `owner_id` is not null" ,nativeQuery = true)
    long countSendByCouponId(String id);


    @Query(value = "SELECT ifnull(COUNT(*),0)  FROM `coupon_instance` WHERE `coupon_id` =? AND `owner_id` =? and `validate_time`  between ? and ? and `stauts`='VERIFIED' " ,nativeQuery = true)
    long countEverDayValidateCount(String id,String ownIn,Date begin,Date end);

    @Query(value = "SELECT ifnull(COUNT(*),0)  FROM `coupon_instance` WHERE `coupon_id` =? AND `owner_id` is not null AND `stauts` ='VERIFIED' " ,nativeQuery = true)
    long countUseByCouponId(String id);

    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsAndCouponIdInOrderByEndTimeDesc(String memberId, String storeId, CouponInstanceStatus stauts, List<String> couponId, Pageable pageable);

    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsAndCouponIdInOrderByValidateTimeDesc(String memberId, String storeId, CouponInstanceStatus stauts, List<String> couponId, Pageable pageable);

    Page<CouponInstance> findListByOwnerIdAndStoreIdAndStautsInAndEndTimeAfterAndCouponIdIn(String memberId, String storeId, List<CouponInstanceStatus> list, Date date, List<String> couponId, Pageable pageable);
}
